-- This demo is normally not done during the presentation due to time constraints,
-- but you are encouraged to do it on your own. It shows the impact of collations
-- when you have a data-type mismatch and for LIKE queries with leading and 
-- trailing wildcards.
-- © Erland Sommarskog 2024.


SET NOCOUNT ON
USE tempdb
go
-- Set up a table with four columns, all with different collations. The name is due
-- to the data we fill the table with.
DROP TABLE IF EXISTS guids
CREATE TABLE guids (
    ident    bigint       NOT NULL IDENTITY,
    Windows_nvarchar nvarchar(200) 
          COLLATE Latin1_General_CI_AS NOT NULL UNIQUE,
    SQL_nvarchar     nvarchar(200) 
          COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL UNIQUE,
    Windows_varchar  varchar(200)  
          COLLATE Latin1_General_CI_AS NOT NULL UNIQUE,
    SQL_varchar      varchar(200)  
          COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL UNIQUE
)
go
-- Fill the table. This can take 20-30 secconds to run.
INSERT guids (Windows_nvarchar, SQL_nvarchar, Windows_varchar, SQL_varchar)
   SELECT TOP (100000) 
          concat_ws(' ', newid(), newid(), newid(), newid()),
          concat_ws(' ', newid(), newid(), newid(), newid()),
          concat_ws(' ', newid(), newid(), newid(), newid()),
          concat_ws(' ', newid(), newid(), newid(), newid())
   FROM   sys.columns a
   CROSS  JOIN sys.columns b
go

-- Run a loop for 500 milliseconds, and see how many lookups you can perform
-- for the different columns. The lookups are range lookups and not point lookups,
-- but if you want, you can change the demo to try that as well.
-- Notice that the number of iterations falls when you move to Windows_varchar,
-- and then drastically for the last column.
DECLARE @t int,
        @i int = 0,
        @d datetime2 = sysdatetime()

WHILE datediff(ms, @d, sysdatetime()) < 500
BEGIN
   SELECT @t = MIN(ident) FROM guids WHERE Windows_nvarchar LIKE N'ABC5%'
   -- SELECT @t = MIN(ident) FROM guids WHERE SQL_nvarchar LIKE N'ABC5%'
   -- SELECT @t = MIN(ident) FROM guids WHERE Windows_varchar LIKE N'ABC5%'
   -- SELECT @t = MIN(ident) FROM guids WHERE SQL_varchar LIKE N'ABC5%'
   SET @i += 1
END
SELECT @i AS [No of iterations]
go
-- You are encouraged to look at the query plans!

-- Now we will test LIKE with leading and trailing wildcards.
-- You can run the below all at once, or once batch a time.
DECLARE @d         datetime2(3) = sysdatetime(),
        @searchstr varchar(50)  = '%abc%',
        @cnt       int
SELECT @cnt = COUNT(*) FROM guids WHERE Windows_nvarchar LIKE @searchstr
PRINT concat('Windows_nvarchar: ', datediff(ms, @d, sysdatetime()), ' ms. ',
             'Count: ', @cnt) 
go
DECLARE @d         datetime2(3) = sysdatetime(),
        @searchstr varchar(50)  = '%abc%',
        @cnt       int
SELECT @cnt = COUNT(*) FROM guids WHERE SQL_nvarchar LIKE @searchstr
PRINT concat('SQL_nvarchar: ', datediff(ms, @d, sysdatetime()), ' ms. ', 
             'Count: ', @cnt) 
go
DECLARE @d         datetime2(3) = sysdatetime(),
        @searchstr varchar(50)  = '%abc%',
        @cnt       int
SELECT @cnt = COUNT(*) FROM guids WHERE Windows_varchar LIKE @searchstr
PRINT concat('Windows_varchar: ', datediff(ms, @d, sysdatetime()), ' ms. ', 
             'Count: ', @cnt) 
go   
-- So far not much difference, but look at this one!
DECLARE @d         datetime2(3) = sysdatetime(),
        @searchstr varchar(50)  = '%abc%',
        @cnt       int
SELECT @cnt = COUNT(*) FROM guids WHERE SQL_varchar LIKE @searchstr
PRINT concat('SQL_varchar: ', datediff(ms, @d, sysdatetime()), ' ms. ', 
             'Count: ', @cnt) 
go
-- Even faster - but result is wrong.
DECLARE @d         datetime2(3) = sysdatetime(),
        @searchstr varchar(50)  = '%abc%',
        @cnt       int
SELECT @cnt = COUNT(*) FROM guids 
       WHERE Windows_nvarchar COLLATE Latin1_General_BIN2 LIKE @searchstr
PRINT concat('Binary collation: ', datediff(ms, @d, sysdatetime()), ' ms. ', 
             'Count: ', @cnt) 
go
-- Trick to get the speed of a binary collation and correct result.
DECLARE @d         datetime2(3) = sysdatetime(),
        @searchstr varchar(50)  = '%abc%',
        @cnt       int
SELECT @cnt = COUNT(*) FROM guids 
       WHERE upper(Windows_nvarchar) COLLATE Latin1_General_BIN2 
             LIKE upper(@searchstr)
PRINT concat('Binary collation with upper: ', 
              datediff(ms, @d, sysdatetime()), ' ms. ', 
             'Count: ', @cnt) 
